﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlMatiere
    {
         public static Matiere ReaderToObject(SqlDataReader dr)
        {
            Matiere _matiere = new Matiere();
            _matiere.Id = int.Parse(dr["Id"].ToString());
            _matiere.Nom = dr["Nom"].ToString();
            _matiere.Coefficient = float.Parse(dr["Coefficient"].ToString());
          
                      
            if (!object.ReferenceEquals(dr["Id_matiere"], DBNull.Value))
            {
                _matiere.module = Module.GetModuleById(int.Parse(dr["Id_module"].ToString()));
            }
            return _matiere;
        }

        public static Matiere GetMatiereById(int id)
        {
            Matiere matiere = new Matiere();
            string strSQL = @"select * from Matiere where UserId=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        matiere = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return matiere;
        }

        public static int insertMatiere(Matiere m)
        {
            int n = 0;
            string strSQL = @"insert into Matiere (Nom,Coefficient,Id_module) values(@nom,@coefficient, @id_module)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
           
                    cmd.Parameters.AddWithValue("@nom", m.Nom);
                    cmd.Parameters.AddWithValue("@coefficient", m.Coefficient);
                    cmd.Parameters.AddWithValue("@Id_module", m.module.Id);

                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateMatiere(Matiere m)
        {
            int n = 0;

            string strSQl = @"UPDATE Matiere SET (@nom IS NULL OR Nom=@nom), (@coefficient IS NULL OR Coefficient=@coefficient) "+
           " Id_module=@Id_module where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();

                    SqlCommand cmd = new SqlCommand(strSQl, cnx);
          
                    cmd.Parameters.AddWithValue("@nom", m.Nom);
                    cmd.Parameters.AddWithValue("@coefficient", m.Coefficient);
                    cmd.Parameters.AddWithValue("@Id_module",m.module.Id);
                    n = cmd.ExecuteNonQuery();

                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletMatiere(int id, string nom, float coefficient, int idmodule)
        {
            int n = 0;
            string strSQL = @"delete from Matiere where id=@id)";
           
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    
                    cmd.Parameters.AddWithValue("@id", id);
                    
                   
                   n=  cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }


        public static List<Matiere> selectListeMatiereByIdNiveau( int idNiveau )
       {
         string strSQL = @"select * from Niveau ";
        using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
         {
               cnx.Open();
                        SqlCommand cmd = new SqlCommand(strSQL, cnx);


            SqlDataReader dr = cmd.ExecuteReader();

          List<Matiere> List_Matiere = new List<Matiere>();
        while (dr.Read())
        {
          List_Matiere.Add(ReaderToObject(dr));
              }
    
                dr.Close();
                cnx.Close();

                return List_Matiere;
            }

        }
        }
    }
   

